/*    
 * Copyright (c) 2013 ISoftStone, Inc.
 * All right reserved.
 *
 * 文件名：      IAccountItemDao.java
 * 作者:     Jacky Wang
 * 创建日期： 2013-7-25 下午03:00:39
 * 版本：           
 *
 */
package com.isoftstone.industry.aaa.core.dao.assist;

import java.sql.Timestamp;
import java.util.List;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.isoftstone.industry.persistance.bean.AccountItem;
import com.isoftstone.modules.core.persistence.IGenericDao;

/**
 *
 * @author jacky
 */
public interface AccountItemDao extends IGenericDao<AccountItem, String>
{
	@Query("select x from AccountItem x where x.productInst.user.id=?1 and (x.billingCycle.id=?2 or (x.createdTime>=?3 and x.createdTime <=?4 and x.billingCycle is null)) order by x.productInst.createTime asc")
	List<AccountItem> findByUserAndBillingCycleOrCreatedTimeBetween(String userId,String billingCycleId, Timestamp start, Timestamp end);

	@Modifying
	@Query("update AccountItem x set x.status=:status where x.id=:id")
	void setStatus(@Param("id")String id, @Param("status") String status);
	
	@Query("select x from AccountItem x where x.productInst.user.id=?1 and x.status=?2 order by x.createdTime asc")
	List<AccountItem> findByUserAndStatus(String userId, String status);

	@Query("select x from AccountItem x where x.productInst.user.id=?1 and x.billingCycle.id=?2 and x.prePaid=?3 order by x.productInst.createTime asc")
	List<AccountItem> findByUserAndBillingCycleAndPrePaid(String userId, String billingCycleId, boolean prePaid);

	@Query("select distinct x from AccountItem x inner join x.billItemAcctItems y inner join y.billItem z where z.bill.id=?1 and x.status=?2 and x.payableAmount>0")
	List<AccountItem> findByBillAndStatusAndPayableGreaterThan0(String billId, String status);
	
//	@Query("select x from AccountItem x where x.productInst.user.id=?1 and x.status=?2 and x.prePaid=true and x.prePaidAmount < x.payableAmount order by x.createdTime asc")
//	List<AccountItem> findPrepaidByUserAndStatusAndPrepaidLessThanPayable(String userId, String status);
	
	@Query("select x from AccountItem x where x.productInst.user.id=?1 and x.status=?2 and x.prePaid=true and x.billingCycle is null and x.payableAmount>0 order by x.createdTime asc")
	List<AccountItem> findOwningPrepaidItemsByUser(String userId, String status);
}
